package com.lunwen.utils;

import com.lunwen.config.SystemConfiguration;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcTemplate {


    private static JdbcTemplate jdbcTemplate;


    private String url = null;
    private String username = null;
    private String password = null;
    private String driver = null;

    /**
     * 使用单例模式
     * @return
     */
    public static JdbcTemplate getJdbcTemplate() {
        synchronized (JdbcTemplate.class){
            if (jdbcTemplate == null){
                jdbcTemplate = new JdbcTemplate();
            }
        }
        return jdbcTemplate;
    }

    private JdbcTemplate(){
        this.url = SystemConfiguration.getProperty("jdbc.url");
        this.username = SystemConfiguration.getProperty("jdbc.username");
        this.password = SystemConfiguration.getProperty("jdbc.password");
        this.driver = SystemConfiguration.getProperty("jdbc.driver");
        try {
            Class.forName(this.driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     * @return
     */
    public Connection getConnection(){
        try {
            return DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 查询 返回map列表
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public List<Map<String,Object>> query(String sql, Object ...params) throws SQLException {
        Connection connection = getConnection();
        PreparedStatement statement = connection.prepareStatement(sql);
        if (params != null){
            for (int i = 0; i < params.length; i++) {
                statement.setObject(i+1,params[i]);
            }
        }

        /**
         * 获取表头
         */
        ResultSet resultSet = statement.executeQuery();
        ResultSetMetaData meta = resultSet.getMetaData();
        String[] header = new String[meta.getColumnCount()];
        for (int i = 0; i < meta.getColumnCount(); i++) {
            header[i] = meta.getColumnName(i+1);
        }

        List<Map<String,Object>> result = new ArrayList<Map<String, Object>>();
        while (resultSet.next()){
            Map<String,Object> map = new HashMap<String, Object>();
            for (int i = 0; i < header.length; i++) {
                map.put(header[i],resultSet.getObject(i+1));
            }
            result.add(map);
        }
        resultSet.close();
        statement.close();
        connection.close();
        return result;
    }


    public <T> List<T> query(String sql, Class<T> cls, Object... params) throws SQLException {
        List<Map<String, Object>> query = query(sql, params);
        List<T> result = new ArrayList<T>();
        for (int i = 0; i < query.size(); i++) {
            try {
                T instance = cls.newInstance();
                Map<String, Object> map = query.get(i);
                map.forEach((key,value)->{
                    try {
                        Field field = instance.getClass().getDeclaredField(getStandardName(key));
                        field.setAccessible(true);
                        field.set(instance,value);
                    } catch (NoSuchFieldException | IllegalAccessException e) {
                        e.printStackTrace();
                    }
                });
                result.add(instance);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    public String getStandardName(String name){
        return name.replace("_","").toLowerCase();
    }


    public int modify(String sql,Object ...params) throws SQLException {
        Connection connection = getConnection();
        PreparedStatement statement = connection.prepareStatement(sql);
        if (params != null){
            for (int i = 0; i < params.length; i++) {
                statement.setObject(i+1,params[i]);
            }
        }
        return statement.executeUpdate();
    }
}
